package com.sihai.ehr.service.impl;

import com.sihai.core.*;
import com.sihai.core.support.HibernateDataAccessor;
import com.sihai.ehr.model.biz.ExamPeriod;
import com.sihai.ehr.model.biz.ExamQualScore;
import com.sihai.ehr.model.biz.ExamStatistics;
import com.sihai.ehr.model.sys.ExaminePeriodEnum;
import com.sihai.ehr.model.sys.Organization;
import com.sihai.ehr.service.ExamAppManageService;
import com.sihai.ehr.web.pojo.ExamEmployeePojo;
import com.sihai.ehr.web.pojo.ExamFacetalkPojo;
import com.sihai.ehr.web.pojo.ExamStatisticsItemPojo;
import com.sihai.ehr.web.pojo.ExamStatisticsPojo;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;


@Service("examAppManageService")
public class ExamAppManageServiceImpl implements ExamAppManageService {

    @Resource
    private DataAccessor dataAccessor;

    @Override
    public LimitedList<ExamFacetalkPojo> findAllEmpStatistics(final ExaminePeriodEnum periodType, final Long period,
                                                              final Long organizationId, final Long dutyId, final Float startScore,
                                                              final Float endScore, final String status, Limit limit, Sorter sorter) {

        Organization organization = null;
        boolean isReadTopOrg = false;
        if (organizationId > 0) {
            try {
                organization = dataAccessor.get(Organization.class,
                        organizationId);
                if (organization.getTopOrganization() == null) {
                    isReadTopOrg = true;
                }
            } catch (Exception e) {
                return null;
            }
        }

        final boolean isReadTopOrganization = isReadTopOrg;
        return dataAccessor.findByQuery(ExamFacetalkPojo.class,
                new QueryCreator() {
                    @Override
                    public Query createQuery(Session session) {
                        StringBuilder hql = new StringBuilder("select new com.sihai.ehr.web.pojo.ExamFacetalkPojo(s.id,e.id,e.workNo,e.baseId.name,e.baseId.sex," +
                                "e.organization.fullName,e.duty.dutyName,e.grade.gradeName,s.isFacetime,s.score,s.prevScore) " +
                                "from ExamStatistics s left join s.employeeinfoWork e where 1=1 ");
                        if ("up".equals(status)) {
                            hql.append("and (s.score between s.prevScore+" + startScore + " and s.prevScore+" + endScore + ") ");
                        } else {
                            hql.append("and (s.score between s.prevScore-" + endScore + " and s.prevScore-" + startScore + ") ");
                        }
                        DynamicQuery dynamicQuery = new DynamicQuery(hql.toString());
                        if (isReadTopOrganization) {
                            dynamicQuery
                                    .appendIfNotNull(
                                            "and (e.organization.topOrganization.id=:organization_id or e.organization.id =:organization_id)",
                                            "organization_id",
                                            organizationId);
                        } else if (organizationId > 0) {
                            dynamicQuery
                                    .appendIfNotNull(
                                            "and e.organization.id =:organization_id",
                                            "organization_id",
                                            organizationId);
                        }

                        if (dutyId != null && dutyId > 0) {
                            dynamicQuery.appendIfNotNull(" and e.duty.id=:dutyId", "dutyId", dutyId);

                        }
                        //考核周期类型
                        dynamicQuery.appendIfNotNull(
                                "and s.examPeriod.periodType =:periodType",
                                "periodType", periodType);
                        dynamicQuery.append(
                                "and s.examPeriod.id =:period_id",
                                "period_id", period);

                        return dynamicQuery.build(session);
                    }
                }, limit, sorter);
    }

    @Override
    public LimitedList<ExamEmployeePojo> findAllStatisticsHistroyData(final Long organizationId,final Long dutyId, ExaminePeriodEnum periodEnum, List<ExamPeriod> examPeriods,Limit limit, Sorter sorter) {

        Organization organization = null;
        boolean isReadTopOrg = false;
        if (organizationId > 0) {
            try {
                organization = dataAccessor.get(Organization.class,
                        organizationId);
                if (organization.getTopOrganization() == null) {
                    isReadTopOrg = true;
                }
            } catch (Exception e) {
                return null;
            }
        }

        final boolean isReadTopOrganization = isReadTopOrg;
        return dataAccessor.findByQuery(ExamEmployeePojo.class,
                new QueryCreator(){
                    @Override
                    public Query createQuery(Session session) {
                        DynamicQuery dynamicQuery=new DynamicQuery("select new com.sihai.ehr.web.pojo.ExamEmployeePojo(e.id,e.baseId.name,e.organization.fullName,e.duty.dutyName,e.grade.gradeName) from ExamStatistics s right join s.employeeinfoWork e where e.cancel<>1");
                        if (isReadTopOrganization) {
                            dynamicQuery
                                    .appendIfNotNull(
                                            "and (e.organization.topOrganization.id=:organization_id or e.organization.id =:organization_id)",
                                            "organization_id",
                                            organizationId);
                        } else if (organizationId > 0) {
                            dynamicQuery
                                    .appendIfNotNull(
                                            "and e.organization.id =:organization_id",
                                            "organization_id",
                                            organizationId);
                        }

                        if (dutyId != null && dutyId > 0) {
                            dynamicQuery.appendIfNotNull(" and e.duty.id=:dutyId", "dutyId", dutyId);

                        }
                        return dynamicQuery.build(session);
                    }
                },limit,sorter);
    }

    @Override
    public List<ExamStatisticsItemPojo> findExamstatisticsItems(ExamPeriod examPeriod, Long workId,Limit limit,Sorter sorter) {
        if (examPeriod == null || workId <= 0) {
			return null;
		}
        return dataAccessor.findByHql(ExamStatisticsItemPojo.class,
                "select new com.sihai.ehr.web.pojo.ExamStatisticsItemPojo(s.qualScore,b.percentageQualitative,s.quanScore,b.percentageQuantitative,s.score) from ExamStatistics s left join s.examBaseConfig b where s.examPeriod.id=? and s.employeeinfoWork.id=? and s.examBaseConfig.id=b.id",
                new Object[]{examPeriod.getId(),workId},limit,sorter);
    }

public LimitedList<ExamStatistics> searchEmployeeExamStatisticsScores(
			final Long empId, final ExaminePeriodEnum periodType,
			final int year, Limit limit, Sorter sorter) {
		if (empId == null || empId <= 0) {
			throw new RuntimeException("参数不正确");
		}
		try {
			return dataAccessor.findByQuery(ExamStatistics.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from ExamStatistics e where 1=1");
							dynamicQuery.append(
									" and e.employeeinfoWork.id = :emp_id",
									"emp_id", empId);
							dynamicQuery
									.append(" and e.examPeriod.periodType = :period_type",
											"period_type", periodType);
							if (year > 0) {
								dynamicQuery
										.append(" and e.examPeriod.examYear = :exam_year",
												"exam_year", year);
							}
							dynamicQuery
									.append(" order by e.examPeriod.examYear asc,e.examPeriod.examMonthly asc");
							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取定性考核总分对象集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamAppManageServiceImpl.searchEmployeeExamStatisticsScores;参数empId="
							+ empId + ",year:" + year + "】");
		}
	}

    @Override
	public LimitedList<ExamStatisticsPojo> searchExamStatisticsPojos(
			final Long organizationId, final Long dutyId,
			final ExaminePeriodEnum periodEnum,
			final List<ExamPeriod> examPeriods, Limit limit, Sorter sorter) {
		LimitedList<ExamStatisticsPojo> examStatisticsPojos = new LimitedList<ExamStatisticsPojo>();
		Organization organization = null;
		boolean isReadTopOrg = false;
		if (organizationId > 0) {
			try {
				organization = dataAccessor.get(Organization.class,
						organizationId);
				if (organization.getTopOrganization() == null) {
					isReadTopOrg = true;
				}
			} catch (Exception e) {
				return null;
			}
		}
		int totalCount = 0;
		String strTotalString = "SELECT count(1)";
		strTotalString += " from employeeinfo_work as w,employeeinfo_base as e,organization as o,duty as d,grade as gr,grade as gt "
				+ "where w.base_id=e.id and w.organization_id=o.id and w.duty_id=d.id and w.grade_id=gr.id and w.lv_id=gt.id ";

		// 定义查询结果列表
		String strSQLString = "SELECT w.id as work_id,e.name,o.full_name,d.duty_name,gr.grade_name,gt.grade_sort,";
		strSQLString += "IFNULL((select score from exam_statistics as s1,exam_period as p1 where s1.period_id=p1.id and p1.exam_year="
				+ examPeriods.get(0).getExamYear()
				+ " and p1.exam_monthly="
				+ examPeriods.get(0).getExamMonthly()
				+ " and s1.work_id=w.id and p1.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'early',";
		strSQLString += "IFNULL((select score from exam_statistics as s2,exam_period as p2 where s2.period_id=p2.id and p2.exam_year="
				+ examPeriods.get(1).getExamYear()
				+ " and p2.exam_monthly="
				+ examPeriods.get(1).getExamMonthly()
				+ " and s2.work_id=w.id and p2.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'previous',";
		strSQLString += "IFNULL((select score from exam_statistics as s3,exam_period as p3 where s3.period_id=p3.id and p3.exam_year="
				+ examPeriods.get(2).getExamYear()
				+ " and p3.exam_monthly="
				+ examPeriods.get(2).getExamMonthly()
				+ " and s3.work_id=w.id and p3.period_type = '"
				+ periodEnum.toString() + "'), 0) AS 'current'";
		strSQLString += " from employeeinfo_work as w,employeeinfo_base as e,organization as o,duty as d,grade as gr,grade as gt "
				+ "where w.base_id=e.id and w.organization_id=o.id and w.duty_id=d.id and w.grade_id=gr.id and w.lv_id=gt.id ";

		if (isReadTopOrg) {
			strTotalString += " and (o.top_organization_id=" + organizationId
					+ " or o.id=" + organizationId + ")";
			strSQLString += " and (o.top_organization_id=" + organizationId
					+ " or o.id=" + organizationId + ")";
		} else {
			strTotalString += " and o.id=" + organizationId;
			strSQLString += " and o.id=" + organizationId;
		}
		if (dutyId > 0) {
			strTotalString += " and d.id=" + dutyId;
			strSQLString += " and d.id=" + dutyId;
		}
		strSQLString += " limit " + limit.getOffset() + "," + limit.getCount();
		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory().getCurrentSession();
		Connection vConnection = vSession.connection();
		Statement vStatement;
		try {
			vStatement = vConnection.createStatement();
			ResultSet rsTotal = vStatement.executeQuery(strTotalString);
			if (rsTotal.next()) {
				totalCount = rsTotal.getInt(1);
			}
			rsTotal.close();

			ResultSet rs = vStatement.executeQuery(strSQLString);
			while (rs.next()) {
				ExamStatisticsPojo statisticsPojo = new ExamStatisticsPojo(
						rs.getLong("work_id"), rs.getString("name"),
						rs.getString("full_name"), rs.getString("duty_name"),
						rs.getString("grade_name"), rs.getString("grade_sort"),
						rs.getFloat("early"), rs.getFloat("previous"),
						rs.getFloat("current"));
				examStatisticsPojos.add(statisticsPojo);
			}
			rs.close();
			vStatement.close();
			vConnection.close();
			examStatisticsPojos.setTotalcount(totalCount);
			return examStatisticsPojos;
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取考核总分对象集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamAppManageServiceImpl.searchExamStatisticsPojos;参数organizationId="
							+ organizationId + ",periodType=" + periodEnum
							+ "】");
		}
	}

    @Override
    public ExamStatistics getExamStatistics(Long id) {
        return dataAccessor.get(ExamStatistics.class,id);
    }
}
